/* check for the cross UOM item exists*/
select * from dbo.Star_UOMConversionFactors a
where  a.unitofmeasure = (select relatedunitofmeasure from dbo.Star_UOMConversionFactors b where a.itemnumber = b.itemnumber and a.unitofmeasure= b.relatedunitofmeasure and a.relatedunitofmeasure = b.unitofmeasure) 

/* insert all UOM not cross */
insert into dbo.Star_UOMConversionFactors

select SOURCE,SourceID,BusinessUnitCode,ItemNumber,RelatedUnitOfMeasure as UnitOfMeasure,UnitOfMeasure as RelatedUnitOfMeasure,UnitOfMeasureStructure,
1.00/ConversionFactor as ConversionFactor,1.00/ConvFactorSecondaryToPrimary as ConvFactorSecondaryToPrimary,UserID,ProgramID,WorkStationID,DateUpdated,
TimeOfDay,ExcludefromPO,ExcludefromSO,CodePurchasePriceCode,CodeSalesPriceCode,InsertIndicator,InsertDate,ChangeIndicator,
ChangeDate,DeleteIndicator,DeleteDate,PurgeDate from dbo.Star_UOMConversionFactors where ConversionFactor <>0 and ConvFactorSecondaryToPrimary<>0
and itemnumber not in (36117, 59375, 37415, 38424,38425,38426,44426,45162,43135)

/* items cross UOM exist(exclude 37415 inserted earlier) */
select * from dbo.Star_UOMConversionFactors
where itemnumber in (36117, 59375, 38424,38425,38426,44426,45162,43135)

/* Compare with UOM conversion*/
select * from dbo.Star_UOMConversionFactors
where itemnumber in (36117, 59375,  38424,38425,38426,44426,45162,43135)
and unitofmeasure <> relatedunitofmeasure


/* Insert Partial exclude 37415*/
insert into dbo.Star_UOMConversionFactors
select SOURCE,SourceID,BusinessUnitCode,ItemNumber,RelatedUnitOfMeasure as UnitOfMeasure,UnitOfMeasure as RelatedUnitOfMeasure,UnitOfMeasureStructure,
1.00/ConversionFactor as ConversionFactor,1.00/ConvFactorSecondaryToPrimary as ConvFactorSecondaryToPrimary,UserID,ProgramID,WorkStationID,DateUpdated,
TimeOfDay,ExcludefromPO,ExcludefromSO,CodePurchasePriceCode,CodeSalesPriceCode,InsertIndicator,InsertDate,ChangeIndicator,
ChangeDate,DeleteIndicator,DeleteDate,PurgeDate from dbo.Star_UOMConversionFactors a
 where  not exists (select relatedunitofmeasure from dbo.Star_UOMConversionFactors b where a.itemnumber = b.itemnumber and a.unitofmeasure= b.relatedunitofmeasure and a.relatedunitofmeasure = b.unitofmeasure) 
 and itemnumber in (36117, 59375, 37415, 38424,38425,38426,44426,45162,43135)
